*** import data from 'people search' i.e. tracking how many times a customer did something in the time period (monthly data)


**1. People search A: see KissMetrics/KissDownloads.xlsx
*************************

clear
insheet using KissMetrics/100913/people-search-Aug12.csv, comma
gen month=8
gen year=2012
gen date=ym(year,month)
format date %tm
drop if id==48507
save Data/Aug12peopleA_raw.dta, replace

clear
insheet using KissMetrics/100913/people-search-Sep12.csv, comma
gen month=9
gen year=2012
gen date=ym(year,month)
format date %tm
save Data/Sep12peopleA_raw.dta, replace

clear
insheet using KissMetrics/100913/people-search-Oct12.csv, comma
gen month=10
gen year=2012
gen date=ym(year,month)
format date %tm
save Data/Oct12peopleA_raw.dta, replace

clear
insheet using KissMetrics/100913/people-search-Nov12.csv, comma
gen month=11
gen year=2012
gen date=ym(year,month)
format date %tm
save Data/Nov12peopleA_raw.dta, replace

clear
insheet using KissMetrics/100913/people-search-Dec12.csv, comma
gen month=12
gen year=2012
gen date=ym(year,month)
format date %tm
save Data/Dec12peopleA_raw.dta, replace

clear
insheet using KissMetrics/100913/people-search-Jan.csv, comma
gen month=1
gen year=2013
gen date=ym(year,month)
format date %tm
save Data/Jan13peopleA_raw.dta, replace

clear
insheet using KissMetrics/100913/people-search-Feb.csv, comma
gen month=2
gen year=2013
gen date=ym(year,month)
format date %tm
save Data/Feb13peopleA_raw.dta, replace

clear
insheet using KissMetrics/100913/people-search-Mar.csv, comma
gen month=3
gen year=2013
gen date=ym(year,month)
format date %tm
save Data/Mar13peopleA_raw.dta, replace

clear
insheet using KissMetrics/100913/people-search-Apr.csv, comma
gen month=4
gen year=2013
gen date=ym(year,month)
format date %tm
save Data/Apr13peopleA_raw.dta, replace

clear
insheet using KissMetrics/100913/people-search-May.csv, comma
gen month=5
gen year=2013
gen date=ym(year,month)
format date %tm
save Data/May13peopleA_raw.dta, replace

clear
insheet using KissMetrics/100913/people-search-Jun.csv, comma
gen month=6
gen year=2013
gen date=ym(year,month)
format date %tm
save Data/Jun13peopleA_raw.dta, replace

clear
insheet using KissMetrics/100913/people-search-Jul.csv, comma
gen month=7
gen year=2013
gen date=ym(year,month)
format date %tm
save Data/Jul13peopleA_raw.dta, replace

clear
insheet using KissMetrics/100913/people-search-Aug.csv, comma
gen month=8
gen year=2013
gen date=ym(year,month)
format date %tm
save Data/Aug13peopleA_raw.dta, replace



clear
insheet using KissMetrics/100913/people-search-22.csv, comma
gen month=7
gen year=2013
gen date=ym(year,month)
format date %tm
save Data/Jul13_rece_peopleA_raw.dta, replace


clear
insheet using KissMetrics/100913/people-search-23.csv, comma
gen month=8
gen year=2013
gen date=ym(year,month)
format date %tm
save Data/Aug13_rece_peopleA_raw.dta, replace


clear
insheet using KissMetrics/030913/people-search_id.csv, comma
save Data/people_id.dta, replace


**2. People search B: see KissMetrics/KissDownloads.xlsx
*************************

clear
insheet using KissMetrics/050913/people-search-13.csv, comma
save Data/ACCNOspeopleB_raw.dta, replace

clear
insheet using KissMetrics/050913/people-search-14.csv, comma
rename v4 activated_date
drop activated
save Data/ActivatedpeopleB_raw.dta, replace

clear
insheet using KissMetrics/050913/people-search-15.csv, comma
rename loggedin loggedin_date
rename v4 Totalloggedin
label variable Totalloggedin "No. of logins by 31Aug2013"
save Data/LoggedinpeopleB_raw.dta, replace

clear
insheet using KissMetrics/050913/people-search-16.csv, comma
rename clicked clicked_date
save Data/ClickedpeopleB_raw.dta, replace

clear
insheet using KissMetrics/050913/people-search-17.csv, comma
rename visitedsite visited_date
rename v4 Totalvisits
label variable Totalvisits "No. of web visits by 31Aug2013"
save Data/VisitedpeopleB_raw.dta, replace

clear
insheet using KissMetrics/050913/people-search-18.csv, comma
gen account_number = real(accountnumber)
gen x=real(v4) if length(v4)==6
gen y=real(v5) if length(v5)==6
replace account_number =x if account_number==.
replace account_number =x if account_number<10000 
drop x y accountnumber v4 v5
save Data/ACCNOsemail_raw.dta, replace

clear
insheet using KissMetrics/050913/people-search-19.csv, comma
rename receivedcampaignemail received_date
rename openedcampaignemail opened_date
save Data/ReceivedpeopleB_raw.dta, replace

clear
insheet using KissMetrics/050913/people-search-20.csv, comma
gen account_number = real(accountnumber)
drop accountnumber
rename v4 account_number_first
rename customerid customer_first
rename v5 customerid
save Data/PeopleB_raw.dta, replace

clear 
insheet using KissMetrics/180314/people-search_viewingconserve.csv, comma
rename accountnumber account_number
drop v4
gen ever_viewedconserve =1
save Data/Viewingconserve_raw.dta, replace
